<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2020/6/3 0003
 * Time: 14:22
 */

namespace app\currency\service;


use think\Db;
use think\Exception;

class CleanerService
{

    /**
     * 设置保洁员工作时间
     * @param string $cid 保洁员ID
     * @param array $week_time_slot [$week => array($time_slot,$time_slot),$week => array($time_slot,$time_slot)]
     * @return array
     */
    public static function setTimePlan($cid,array $week_time_slot){
        $time_plan = [];
        foreach ($week_time_slot as $key => $item) {
            sort($item);
            if(!in_array($key,TimeSlotService::$week)){
                continue;
            }
            $item = array_filter($item,function ($v){
               return $v < 6;
            });
            if(empty($item)){
                continue;
            }
            $time_plan[] = [
                'cid' => $cid,
                'week' => $key,
                'time_slot' => implode($item,',')
            ];
        }
        try{
            Db::transaction(function () use($cid,$time_plan){
                Db::name('CleanerTimePlan')->where('cid','eq',$cid)->delete();
                if(!empty($time_plan)){
                    Db::name('CleanerTimePlan')->insertAll($time_plan);
                }
            });
        } catch (\Exception $e){
            return ['code' => 0,'msg' => $e->getMessage()];
        }
        return ['code' => 1,'msg' => '设置保洁员工作时间成功！'];
    }

    /**
     * 获取保洁员工作时间
     * @param int $cid 保洁员ID
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getTimePlan($cid){
        $res = [];
        $time_plan = Db::name('CleanerTimePlan')->where('cid','eq',$cid)->field('week,time_slot')->select();
        foreach ($time_plan as $plan) {
//            $res[$plan['week']] = array_map(function($v){
//                return self::$time_slot[$v];
//            },explode(',',$plan['time_slot']));
            $res[$plan['week']] = $plan['time_slot'];
        }
        return $res;
    }

    /**
     * 获取保洁员当前可用的工作时间
     * @param int $cid
     * @return array|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getAvailableTimeSlot($cid)
    {
        //工作状态
        if(Db::name('Cleaner')->where([['id','eq',$cid],['work_state','eq','1'],['auth_state','eq','1']])->count() < 1){
            return [];
        }
        //查询保洁员工作时间设置
        $time_plan = Db::name('CleanerTimePlan')->where('cid','eq',$cid)->field('week,time_slot')->select();
        //查询保洁员正在工作的订单
        $order_time = Db::name('CleanOrder')->where([['cid','eq',$cid],['status','eq','2']])->where('service_date', '<= time', date('Y-m-d',strtotime('+1 week')))->field('service_date,time_slot')->select();
        foreach ($order_time as &$order) {
            $week = date('w',strtotime($order['service_date']));
            if($week == '0') $week = '7';
            $order['week'] = $week;
        }
//        return $order_time;
        $today_week = date('w');
        if($today_week == '0') $today_week = '7';
        foreach ($time_plan as &$time) {
            if($time['week'] == $today_week)
            {
                $time['time_slot'] = implode(',',array_filter(explode(',',$time['time_slot']),function($v){
                    $time_slot = TimeSlotService::$time_slot;
                    $time_slot_selected = explode('-',$time_slot[$v]);
                    return time() < strtotime(date('Y-m-d '.$time_slot_selected[0].':00'));
                }));
            }
            foreach ($order_time as $order_item) {
                if($time['week'] == $order_item['week'])
                {
                    $time['time_slot'] = implode(',',array_filter(explode(',',$time['time_slot']),function($v) use($order_item){
                        return $order_item['time_slot'] != $v;
                    }));
                }
            }
        }
        return $time_plan;
    }

    /**
     * 筛选可工作状态的保洁员
     * 按距离排序
     * @param string $service_date 服务日期
     * @param int $time 时间编号
     * @param string $center 经纬度，经度在前维度在后“,”分割的坐标字符串 106.5017,29.533902
     * @param int $mid 用户ID 用于筛选拉黑的保洁员
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getFreeCleaner($service_date, $time, $center, $mid = 0)
    {
        if(!isset(TimeSlotService::$time_slot[$time])){
            return [];
        }
        //筛选出工作时间匹配的保洁员
        $week = date("w",strtotime($service_date));
        if($week == '0') $week = '7';
        $time_plan = Db::name('CleanerTimePlan')->where('week','eq',$week)->where("FIND_IN_SET({$time},time_slot)")->select();
        $cids = array_unique(array_column($time_plan,'cid'));
        if($mid > 0)
        {
            //查询用户拉黑记录
            $member_blacklist = Db::name('MemberBlacklist')->where('mid','eq',$mid)->select();
            //移除黑名单
            $cids = array_diff($cids,array_unique(array_column($member_blacklist,'cid')));
        }
        //移除正在上工或者休息中的保洁员
        $db = Db::name('Cleaner')->alias('a')
            ->where([['a.id','in',$cids],['a.work_state','eq','1'],['a.auth_state','eq','1']])
            ->whereNotExists("SELECT `b`.`id` FROM `clean_order` `b` WHERE `a`.`id` = `b`.`cid` AND `b`.`time_slot` = '{$time}' AND `b`.`service_date` = '{$service_date}'");
        $center = array_filter(explode(',',$center));
        list($lng, $lat) = is_array($center) && count($center) > 1 ? $center : ['',''];
        if(empty($lat) || empty($lng))
        {
            return [];
        }
        //距离排序
        $db->field("*,ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            {$lat} * PI() / 180 - lat * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS({$lat} * PI() / 180) * COS(lat * PI() / 180) * POW(
                    SIN(
                        (
                            {$lng} * PI() / 180 - lng * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS distance")->order('distance asc');
        $cleaner = (array)$db->limit(10)->select();
        return $cleaner;
    }

    /**
     * 获取保洁员下属团队数量
     * @param int $cid 保洁员ID
     * @param int $level 0两级全部人数 1一级会员人数 2二级会员人数
     * @param boolean $isAll 是否全部返回 false 返回指定级别会员 true 返回指定级别内所有会员
     * @return mixed
     */
    public static function getTeamNum($cid,$level = 0, $isAll = false)
    {
        $level = (int)$level;
        $level < 0 && $level = 0;
        if ($level > 0)
        {
            if($isAll)
            {
                $level++;
                $sql = "SELECT COUNT(*) AS count FROM (
SELECT T1.id,T1.`status`,if(FIND_IN_SET(pid,@pids) > 0,@l := @l +1,0) AS lvl,if(FIND_IN_SET(pid,@pids) > 0,@pids := CONCAT(@pids,',',id),0) as ischild
 FROM (SELECT id,pid,`status` FROM cleaner T WHERE 1 = 1) T1,(SELECT @pids := {$cid},@l := 0) T2
 ) T3 WHERE ischild != 0 AND `status` != 0 AND lvl < {$level}";
            } else {
                $sql = "SELECT COUNT(*) AS count FROM (
SELECT T1.id,T1.`status`,if(FIND_IN_SET(pid,@pids) > 0,@l := @l +1,0) AS lvl,if(FIND_IN_SET(pid,@pids) > 0,@pids := CONCAT(@pids,',',id),0) as ischild
 FROM (SELECT id,pid,`status` FROM cleaner T WHERE 1 = 1) T1,(SELECT @pids := {$cid},@l := 0) T2
 ) T3 WHERE ischild != 0 AND `status` != 0 AND lvl = {$level}";
            }

        }
        else
        {
            if($isAll)
            {
                $sql = "SELECT COUNT(*) AS count FROM (
SELECT T1.id,T1.`status`,if(FIND_IN_SET(pid,@pids) > 0,@pids := CONCAT(@pids,',',id),0) as ischild
 FROM (SELECT id,pid,`status` FROM cleaner T WHERE 1 = 1) T1,(SELECT @pids := {$cid}) T2
 ) T3 WHERE ischild != 0 AND `status` != 0";
            } else {
                return 0;
            }

        }
        $num = Db::query($sql)[0]['count'];
        return $num;
    }

    /**
     * 获取下级用户的id集合
     * @param int $cid
     * @param int $level
     * @return array
     */
    public static function getTeamIds($cid, $level)
    {
        $level = (int)$level;
        $level < 0 && $level = 0;
        if($level > 0)
        {
            $level++;
            $sql = "SELECT id FROM (
SELECT T1.id,T1.`status`,if(FIND_IN_SET(pid,@pids) > 0,@l := @l +1,0) AS lvl,if(FIND_IN_SET(pid,@pids) > 0,@pids := CONCAT(@pids,',',id),0) as ischild
 FROM (SELECT id,pid,`status` FROM cleaner T WHERE 1 = 1) T1,(SELECT @pids := {$cid},@l := 0) T2
 ) T3 WHERE ischild != 0 AND `status` != 0 AND lvl < {$level}";
        }
        else
        {
            $sql = "SELECT id FROM (
SELECT T1.id,T1.`status`,if(FIND_IN_SET(pid,@pids) > 0,@pids := CONCAT(@pids,',',id),0) as ischild
 FROM (SELECT id,pid,`status` FROM cleaner T WHERE 1 = 1) T1,(SELECT @pids := {$cid}) T2
 ) T3 WHERE ischild != 0 AND `status` != 0";
        }
        $result = Db::query($sql);
        if($result)
        {
            return array_unique(array_column($result,'id'));
        } else {
            return [];
        }
    }

    /**
     * 获取上级用户的ID集合
     * @param $cid
     * @param $level
     * @return array
     */
    public static function getParentIds($cid,$level)
    {
        $level++;
        $sql = "SELECT T2.id,T1.lvl FROM (
SELECT @r AS _id,
(SELECT @r := pid FROM cleaner WHERE id = _id) AS pid,
@l := @l + 1 AS lvl FROM (SELECT @r := {$cid},@l := -1) vars,cleaner h WHERE @r <> 0
) T1 JOIN cleaner T2 ON T1._id = T2.id WHERE T1.lvl > 0 AND T1.lvl < {$level} ORDER BY T1.lvl DESC";
        $result = Db::query($sql);
        if($result){
            return (array)$result;
        } else {
            return [];
        }
    }

    /**
     * 获取代理商信息
     * @param $cid
     * @return int|mixed
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getAgent($cid)
    {
        if($cid == 0)
        {
            return 0;
        }
        $cleaner = Db::name('Cleaner')->where('id',$cid)->field('id,agent_id,pid')->find();
        if(!empty($cleaner))
        {
            if($cleaner['agent_id'] != '0')
            {
                return $cleaner['agent_id'];
            } else {
                return self::getAgent($cleaner['pid']);
            }
        } else {
            return 0;
        }
    }
    /**
     * 获取保洁员绑定地址数量
     * @param int $cid 保洁员ID
     * @return mixed
     */
    public static function getBindAddNum($cid)
    {
        return Db::name('CitiesCleaner')
            ->where('cid','eq',$cid)
            ->count();
    }

    /**
     * 获取待接单订单数量
     * @param int $cid 保洁员ID
     * @return float|int|string
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getOrderTaking($cid)
    {
        $cleaner = Db::name('Cleaner')->where('id',$cid)->find();
        if(empty($cleaner))
        {
            return 0;
        }
        $getAvailableTimeSlot = self::getAvailableTimeSlot($cid);
        if($cleaner['work_state'] == '0' || $cleaner['auth_state'] == '0' || empty($getAvailableTimeSlot))
        {
            return 0;
        }
        $whereOr = [];
        foreach ($getAvailableTimeSlot as $key => $item) {
            $week = $item['week'];
            if($week == '7') $week = '0';
            $week++;
            $whereOr[$key][] = ['time_slot','in',$item['time_slot']];
            $whereOr[$key][] = ['','exp',Db::raw("DAYOFWEEK(service_date) = {$week}")];
        }
        $where = [];
        $where[] = ['status','eq','1'];
        //计算非指定或者指定自己的
//        $where[] = ['appoint_cid','in',['0',$cid]];//
        //排除已经被拉黑的
        $member_blacklist = Db::name('MemberBlacklist')->where('cid','eq',$cid)->select();
        $blacklist_mids = array_unique(array_column($member_blacklist,'mid'));
        if(!empty($blacklist_mids))
        {
            $where[] = ['mid','not in',$blacklist_mids];
        }
        $num = Db::name('CleanOrder')
            ->where($where)
            ->where(function ($query) use($whereOr){
                $query->whereOr($whereOr);
            })
            ->where(function ($query) use($cid){
                $query->whereOr([
                    [
                        ['appoint_cid','eq',$cid],
                        ['cleaner_cancel_status','eq','0']
                    ],
                    [
                        ['appoint_cid','eq','0']
                    ],
                    [
                        ['appoint_cid','not in',['0',$cid]],
                        ['cleaner_cancel_status','eq','1']
                    ]
                ]);
            })
            ->count();
        return $num;
    }
}